package com.credit.shtl.dao.curri;/**
 * @Classname CurriDAO
 * @Description 课程信息DAO接口
 * @Date 2020/10/21 21:20
 * @Created by SHTL
 */


import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * @InterfaceName CurriDAO
 * @Description TODO
 * @Author SHTL
 * @Date 2020/10/21 21:20
 * @Version 1.0
 */
public interface CurriDAO extends JpaRepository<CurriModel,Integer>,
        JpaSpecificationExecutor<CurriModel> {

    CurriModel findByKechengid(Integer kechengid);


    // 检测同一上课时间选择的上课教室是否冲突
    @Modifying
    @Query(value = "select * from curriculum_table as c where c.classroom = :classroomname and c.classweek = :classweek and c.classsection = :classsection and ((c.startweek <= :startweek and c.endweek <= :endweek) or (c.startweek <= :startweek and c.endweek >= :endweek) or (c.startweek >= :startweek and c.endweek <= :endweek) or (c.startweek >= :startweek and c.endweek >= :endweek))",nativeQuery = true)
    List<CurriModel> classroomcheck_curri(@Param("classroomname") String classroomname,@Param("classweek") String classweek,@Param("classsection") String classsection,@Param("startweek") String startweek,@Param("endweek") String endweek);


    // 根据当前登录的教师账号，获取该教师开设的所有课程中的选课人数
    @Modifying
    @Query(value = "select c.kechengname,(select COUNT(*) from selection_table as s where s.kechengid=c.kechengid) as '选课人数' from curriculum_table as c where c.kechengteacher=:kechengteacher",nativeQuery = true)
    List<Map<String,Object>> getSelectionNum(@Param("kechengteacher") Integer kechengteacher);



    // 根据课程id进行删除
    @Transactional
    @Modifying
    @Query(value = "delete from curriculum_table as a where kechengid=:kechengid",nativeQuery = true)
    Integer deleteByKechengid(@Param("kechengid") Integer kechengid);


    /**
     * 获取当前账户的可选课程信息
     * **/
    // 1. 没有查询条件
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriNo(@Param("xuehao") Integer xuehao);

    // 2. 只有筛选条件为 课程类型
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and b.kechengleibie=:kechengleibie ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriLeixing(@Param("xuehao") Integer xuehao,@Param("kechengleibie") String kechengleibie);

    // 3. 只有筛选条件为 模糊查询
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriLike(@Param("xuehao") Integer xuehao,@Param("like") String like);

    // 4. 只有筛选条件为学分
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and b.xuefen=:xuefen ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriXuefen(@Param("xuehao") Integer xuehao,@Param("xuefen") Integer xuefen);

    // 5. 筛选条件为 学分 和 模糊查询
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) and b.xuefen=:xuefen ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriXandLike(@Param("xuehao") Integer xuehao,@Param("like") String like,@Param("xuefen") Integer xuefen);

    // 6. 筛选条件为 学分 和 课程类型
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and b.xuefen=:xuefen and b.kechengleibie=:kechengleibie ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriXandLeixing(@Param("xuehao") Integer xuehao,@Param("xuefen") Integer xuefen,@Param("kechengleibie") String kechengleibie);

    // 7. 筛选条件为 模糊查询 和 课程类型
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) and b.kechengleibie=:kechengleibie ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriLandLeibie(@Param("xuehao") Integer xuehao,@Param("like") String like,@Param("kechengleibie") String kechengleibie);

    // 8. 筛选条件为 模糊查询 和 学分 以及 课程类型
    @Modifying
    @Query(value = "select b.ctime,b.kechengid,b.classsection,b.kechengname,b.xuefen,(SELECT t.`name` from teacher_table as t where b.kechengteacher=t.zhanghu) as 'teachername',b.kechengleibie,b.startweek,b.endweek,b.classroom,b.classweek,(SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao) as 'selection'  from curriculum_table as b where b.optionalclass like CONCAT('%',(select a.banji from student_table as a where a.xuehao=:xuehao),'%') and (b.kechengid like concat('%',:like,'%') or b.kechengname like concat('%',:like,'%')) and b.kechengleibie=:kechengleibie and b.xuefen:xuefen ORDER BY (SELECT COUNT(*) from selection_table as s where s.kechengid=b.kechengid and s.xuehao=:xuehao)",nativeQuery = true)
    List<Map<String,Object>> findCurriAll(@Param("xuehao") Integer xuehao,@Param("like") String like,@Param("kechengleibie") String kechengleibie,@Param("xuefen") Integer xuefen);
}
